package net.zjitc.dao.impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.domain.Product;
import net.zjitc.utils.C3p0Utils;
import net.zjitc.utils.PageBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by rj1502 on 2017/10/23.
 */
public class ProductDaoImpl implements ProductDao {
    @Override
    public List<Product> findAll() {
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        String sql = "select * from product";
        try {
            List<Product> list = runner.query(sql, new BeanListHandler<Product>(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int insert(Product product) {
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        String sql = "insert into product (pname ,marketprice ,shopprice,pdesc) values (?,?,?,?)";
        int count = 0;
        try {
             count = runner.update(sql,product.getPname(),product.getMarketprice(),product.getShopprice(),product.getPdesc());

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public Product findById(int pid) {
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        String sql = "select * from product where pid = ?";
        Product p =null;
        try {
            p = (Product) runner.query(sql,new BeanHandler(Product.class),pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

    @Override
    public int update(Product p) {
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        String sql = "update product set pname = ?,marketprice = ?,shopprice=?,pdesc=? where pid=?";
        int count = 0;
        try {
            count = runner.update(sql,p.getPname(),p.getMarketprice(),p.getShopprice(),p.getPdesc(),p.getPid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public List<Product> findByCondition(String condition) {
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        String sql = "select * from product where pname like ?";
        String sqlCondition = "%"+condition+"%";
        List<Product> list = null;
        try {
           list = (List<Product>) runner.query(sql,new BeanListHandler(Product.class),sqlCondition);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public PageBean<Product> findPage(int currentPage, int pageSize) {
        /*创建一个对象*/
        PageBean<Product> pageBean = new PageBean<Product>();


        pageBean.setCurrenPage(currentPage);
        pageBean.setPageSize(pageSize);
        QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
        //需要两 条查询语句，
        /*分页查询*/
        String sql = "select * from product order by pid limit ?,?";
        /*查询总数*/
        String countSql = "SELECT COUNT(*) FROM product";
        try {
            List<Product> list = (List<Product>) runner.query(sql,new BeanListHandler(Product.class),(currentPage-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            Long c = (Long) runner.query(countSql,new ScalarHandler());
            int count = c.intValue();
            pageBean.setCount(count);
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //记得返回
        return pageBean;
    }


}
